from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
from copy import deepcopy

wb = Workbook()
ws = wb.active

rows = [
    ('number','Batch1', 'Batch2'),
    (1, 40, 30),
    (2, 50, 40),
    (3, 60, 50),
    (4, 70, 60),
    (5, 80, 70),
    (6, 90, 80),
    (7, 100, 90),
]
for row in rows:
    ws.append(row)

# 图表一
chart1 = BarChart()
# 图表类型 垂直（默认）
chart1.type = "col"
# 设置图标样式 1-48
chart1.style = 48
# 图表标题
chart1.title = "图表1"
# 设置x轴和y轴的描述
chart1.x_axis.title = "月份"
chart1.y_axis.title = "销售额"
# 图表位置
data = Reference(ws, min_col=2, min_row=1, max_row=8, max_col=3)
# 引用工作表的单元范围
labels = Reference(ws, min_col=1, min_row=2, max_row=8)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(labels)
ws.add_chart(chart1, "A10")


# 图表二
chart2 = deepcopy(chart1)
chart2.style = 20
chart2.type = 'bar'
chart2.title = "图表2"
ws.add_chart(chart2, "j10")


# 图表三
chart3 = deepcopy(chart1)
# 图表类型
chart3.type = 'col'
# 更改样式
chart3.style = 10
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = "图表3"
ws.add_chart(chart3, "A27")


# 图表四
chart4 = deepcopy(chart1)
chart4.type = 'bar'
chart4.style = 33
# 带有百分比的
chart4.grouping = "percentStacked"
chart4.title = "图表4"
ws.add_chart(chart4, "j27")

wb.save('2dchart.xlsx')






